﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using YanZhiwei.DotNet2.Interfaces.DataAccess;
namespace YanZhiwei.DotNet2.Utilities.Common
{
    /// <summary>
    /// Access 帮助类
    /// </summary>
    public sealed class AccessHelper : ISQLHelper
    {
        #region 构造函数
        string ConnectString = string.Empty;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="path">access路径</param>
        public AccessHelper(string path)
        {
            ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="path">access路径</param>
        /// <param name="password">access密码</param>
        public AccessHelper(string path, string password)
        {
            ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password= " + password;
        }
        #endregion
        #region ExecuteNonQuery
        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sql">查询，修改，删除sql语句</param>
        /// <param name="parameters">OleDbParameter参数；eg: new OleDbParameter("@categoryName","Test2")</param>
        /// <returns>操作影响行数</returns>
        public int ExecuteNonQuery(string sql, DbParameter[] parameters)
        {
            int _affectedRows = -1;
            using (OleDbConnection sqlcon = new OleDbConnection(ConnectString))
            {
                sqlcon.Open();
                using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))
                {
                    if (parameters != null)
                        sqlcmd.Parameters.AddRange(parameters);
                    _affectedRows = sqlcmd.ExecuteNonQuery();
                }
            }
            return _affectedRows;
        }
        #endregion
        #region ExecuteReader
        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="sql">读取sql语句</param>
        /// <param name="parameters">OleDbParameter参数；eg: new OleDbParameter("@categoryName","Test2")</param>
        /// <returns>IDataReader</returns>
        public IDataReader ExecuteReader(string sql, DbParameter[] parameters)
        {
            OleDbConnection _sqlcon = new OleDbConnection(ConnectString);
            using (OleDbCommand _sqlcmd = new OleDbCommand(sql, _sqlcon))
            {
                if (parameters != null)
                    _sqlcmd.Parameters.AddRange(parameters);
                _sqlcon.Open();
                return _sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion
        #region ExecuteDataTable
        /// <summary>
        /// ExecuteDataTable
        /// </summary>
        /// <param name="sql">读取sql语句</param>
        /// <param name="parameters">OleDbParameter参数；eg: new OleDbParameter("@categoryName","Test2")</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string sql, DbParameter[] parameters)
        {
            using (OleDbConnection _sqlcon = new OleDbConnection(ConnectString))
            {
                using (OleDbCommand _sqlcmd = new OleDbCommand(sql, _sqlcon))
                {
                    if (parameters != null)
                        _sqlcmd.Parameters.AddRange(parameters);
                    using (OleDbDataAdapter _sqldap = new OleDbDataAdapter(_sqlcmd))
                    {
                        DataTable _dt = new DataTable();
                        _sqldap.Fill(_dt);
                        return _dt;
                    }
                }
            }
        }
        #endregion
        #region ExecuteScalar
        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="sql">查询第一行第一列数据值</param>
        /// <param name="parameters">OleDbParameter参数；eg: new OleDbParameter("@categoryName","Test2")</param>
        /// <returns>Object</returns>
        public Object ExecuteScalar(string sql, DbParameter[] parameters)
        {
            using (OleDbConnection _sqlcon = new OleDbConnection(ConnectString))
            {
                using (OleDbCommand _sqlcmd = new OleDbCommand(sql, _sqlcon))
                {
                    if (parameters != null)
                        _sqlcmd.Parameters.AddRange(parameters);
                    _sqlcon.Open();
                    return _sqlcmd.ExecuteScalar();
                }
            }
        }
        #endregion


        public IDataReader ExecuteReader_SP(string proName, DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }
    }
}